const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.roleModel');
const CodeService = require('../services/codeService');
const { checkIfSuperAdmin } = require('../utils/utils');
const cacheService = require('../cache/cacheService');
const { generateCacheKey } = require('../cache/utils/cacheKeyGenerator');
// 判断是否为超级管理员角色的辅助函数
const isSuperAdminRole = (role) => {
    return role && (role.code === 'SUPER_ADMIN' || (role.is_system === 1 && role.is_protected === 1));
};

class RoleModel {
    // 获取所有角色
    static async getAllRoles(page = 1, limit = 10, filters = {}, currentUserId = null, maxPermissionLevel = null) {
        const offset = (page - 1) * limit;
        let whereClause = 'WHERE r.is_delete = 0 AND r.is_protected = 0';
        let params = [];

        // 检查当前用户是否为超级管理员
        const isSuperAdminUser = currentUserId ? await checkIfSuperAdmin(currentUserId, db, logger) : false;

        // 非超级管理员不能看到超级管理员角色
        if (!isSuperAdminUser) {
            whereClause += ' AND NOT (r.code = "SUPER_ADMIN" OR (r.is_system = 1 AND r.is_protected = 1))';
        }

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND r.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND r.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND r.status = ?';
            params.push(filters.status);
        }
        if (filters.is_protected !== undefined) {
            whereClause += ' AND r.is_protected = ?';
            params.push(filters.is_protected);
        }
        if (filters.role_type !== undefined) {
            whereClause += ' AND r.role_type = ?';
            params.push(filters.role_type);
        }
        if (filters.data_scope !== undefined) {
            whereClause += ' AND r.data_scope = ?';
            params.push(filters.data_scope);
        }
        // 搜索值
        if (filters.searchvalue) {
            whereClause += ' AND (r.name LIKE ? OR r.code LIKE ?)';
            params.push(`%${filters.searchvalue}%`, `%${filters.searchvalue}%`);
        }

        // 权限层级过滤
        if ((maxPermissionLevel !== undefined && maxPermissionLevel !== null) && !isNaN(maxPermissionLevel) && !isSuperAdminUser) {
            whereClause += ' AND r.permission_level <= ?';
            params.push(maxPermissionLevel);
        }
        // 也支持从filters中获取maxPermissionLevel（保持兼容性）
        if (filters.maxPermissionLevel !== undefined && !isNaN(filters.maxPermissionLevel) && !isSuperAdminUser) {
            whereClause += ' AND r.permission_level <= ?';
            params.push(filters.maxPermissionLevel);
        }

        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.dept_id,
                d.name as department_name,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.create_by,
                r.update_by,
                (SELECT name FROM user WHERE id = r.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = r.update_by AND is_delete = 0) as update_by_name,
                r.remark,
                r.effective_time,
                r.expire_time,
                r.created_at,
                r.updated_at,
                COUNT(ur.user_id) as user_count,
                COUNT(rm.menu_id) as menu_count
            FROM role r
            LEFT JOIN user_role ur ON r.id = ur.role_id
            LEFT JOIN role_menu rm ON r.id = rm.role_id
            LEFT JOIN department d ON r.dept_id = d.id AND d.is_delete = 0
            ${whereClause}
            GROUP BY r.id, r.name, r.code, r.role_type, r.data_scope, r.dept_id, d.name, r.description, r.status, r.is_protected, r.is_system, r.permission_level, r.create_by, r.update_by, r.remark, r.effective_time, r.expire_time, r.created_at, r.updated_at
            ORDER BY r.created_at DESC
            LIMIT ${Number(limit)} OFFSET ${Number(offset)}
        `;

        try {
            const [rows] = await db.execute(sql, params);
            return rows;
        } catch (error) {
            logger.error('获取所有角色失败', { error });
            throw error;
        }
    }

    // 获取角色总数
    static async getRoleCount(filters = {}, currentUserId = null, maxPermissionLevel = null) {
        let whereClause = 'WHERE r.is_delete = 0';
        let params = [];

        // 检查当前用户是否为超级管理员
        const isSuperAdminUser = currentUserId ? await checkIfSuperAdmin(currentUserId, db, logger) : false;

        // 非超级管理员不能看到超级管理员角色
        if (!isSuperAdminUser) {
            whereClause += ' AND NOT (r.code = "SUPER_ADMIN" OR (r.is_system = 1 AND r.is_protected = 1))';
        }

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND r.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND r.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND r.status = ?';
            params.push(filters.status);
        }
        if (filters.is_protected !== undefined) {
            whereClause += ' AND r.is_protected = ?';
            params.push(filters.is_protected);
        }
        // 权限层级过滤
        if ((maxPermissionLevel !== undefined && maxPermissionLevel !== null) && !isNaN(maxPermissionLevel) && !isSuperAdminUser) {
            whereClause += ' AND r.permission_level <= ?';
            params.push(maxPermissionLevel);
        }
        // 也支持从filters中获取maxPermissionLevel（保持兼容性）
        if (filters.maxPermissionLevel !== undefined && !isNaN(filters.maxPermissionLevel) && !isSuperAdminUser) {
            whereClause += ' AND r.permission_level <= ?';
            params.push(filters.maxPermissionLevel);
        }

        const sql = `SELECT COUNT(*) as total FROM role r ${whereClause}`;

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].total;
        } catch (error) {
            logger.error('获取角色总数失败', { error });
            throw error;
        }
    }

    // 根据ID获取角色
    static async findById(id, currentUserId = null) {
        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.data_scope_dept_ids,
                r.dept_id,
                d.name as department_name,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.create_by,
                r.update_by,
                (SELECT name FROM user WHERE id = r.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = r.update_by AND is_delete = 0) as update_by_name,
                r.remark,
                r.effective_time,
                r.expire_time,
                r.created_at,
                r.updated_at
            FROM role r
            LEFT JOIN department d ON r.dept_id = d.id AND d.is_delete = 0
            WHERE r.id = ? AND r.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            const role = rows[0] || null;


            return role;
        } catch (error) {
            logger.error('根据ID获取角色失败', { error });
            throw error;
        }
    }

    // 根据编码获取角色
    static async findByCode(code) {
        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.dept_id,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.create_by,
                r.update_by,
                (SELECT name FROM user WHERE id = r.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = r.update_by AND is_delete = 0) as update_by_name,
                r.remark,
                r.effective_time,
                r.expire_time,
                r.created_at,
                r.updated_at
            FROM role r
            WHERE r.code = ? AND r.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [code]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据编码获取角色失败', { error });
            throw error;
        }
    }

    // 根据名称获取角色
    static async findByName(name) {
        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.dept_id,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.create_by,
                r.update_by,
                (SELECT name FROM user WHERE id = r.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = r.update_by AND is_delete = 0) as update_by_name,
                r.remark,
                r.effective_time,
                r.expire_time,
                r.created_at,
                r.updated_at
            FROM role r
            WHERE r.name = ? AND r.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [name]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据名称获取角色失败', { error });
            throw error;
        }
    }

    // 检查角色编码是否存在（兼容旧代码，内部调用CodeService）
    static async isCodeExists(code, excludeId = null) {
        try {
            // 如果提供了excludeId，我们需要特殊处理，先使用CodeService检查
            let exists = await CodeService.checkCodeExists('ROLE_CODE', code);

            // 如果CodeService发现存在，但提供了excludeId，我们需要进一步检查是否是要排除的记录
            if (exists && excludeId) {
                const sql = 'SELECT id FROM role WHERE code = ? AND id = ? AND is_delete = 0';
                const [rows] = await db.execute(sql, [code, excludeId]);
                // 如果找到的是要排除的记录，则返回false
                if (rows.length > 0) {
                    return false;
                }
            }

            return exists;
        } catch (error) {
            logger.error('检查角色编码是否存在失败', { error, code, excludeId });
            throw error;
        }
    }

    // 检查角色名称是否存在
    static async isNameExists(name, excludeId = null) {
        let sql = 'SELECT COUNT(*) as count FROM role WHERE name = ? AND is_delete = 0';
        let params = [name];

        if (excludeId) {
            sql += ' AND id != ?';
            params.push(excludeId);
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].count > 0;
        } catch (error) {
            logger.error('检查角色名称是否存在失败', { error });
            throw error;
        }
    }

    // 创建角色
    static async create(roleData, currentUserId = null) {
        let { name, code, role_type = 'custom', permission_level = 0, data_scope = 4, data_scope_dept_ids = null, dept_id = null, description = '', status = 0, is_protected = 0, is_system = 0, create_by = currentUserId, update_by = currentUserId, remark = null, effective_time = null, expire_time = null } = roleData;
        // 检查权限层级是否有效
        if (permission_level < 0 || permission_level > 30 || ![0, 10, 20, 30].includes(permission_level)) {
            throw new Error('无效的权限层级');
        }

        // 如果没有提供code，则自动生成角色编码（使用generateUniqueCode确保编码唯一）
        if (!code) {
            try {
                code = await CodeService.generateUniqueCode('ROLE_CODE');
                logger.info('自动生成角色编码', { code, roleName: name });
            } catch (error) {
                logger.error('生成角色编码失败', { error, roleName: name });
                throw new Error('生成角色编码失败：' + error.message);
            }
        } else {
            // 如果提供了code，则检查编码是否已存在
            const codeExists = await CodeService.checkCodeExists('ROLE_CODE', code);
            if (codeExists) {
                logger.error('角色编码已存在', { code, roleName: name });
                throw new Error(`角色编码 ${code} 已存在，请更换其他编码`);
            }
        }

        const sql = `
            INSERT INTO role (name, code, role_type, data_scope, data_scope_dept_ids, dept_id, description, status, is_protected, is_system, permission_level, create_by, update_by, remark, effective_time, expire_time)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;
        const params = [name, code, role_type, data_scope, data_scope_dept_ids, dept_id, description, status, is_protected, is_system, permission_level, create_by, update_by, remark, effective_time, expire_time];

        try {
            const [result] = await db.execute(sql, params);
            return result.insertId;
        } catch (error) {
            logger.error('创建角色失败', { error });
            throw error;
        }
    }

    // 更新角色
    static async update(id, updateData, currentUserId = null) {
        const { name, code, role_type, data_scope, data_scope_dept_ids, dept_id, description, status, is_protected = 0, permission_level, remark, effective_time, expire_time, update_by = currentUserId, create_by } = updateData;
        // 查询角色信息（用于检查是否为超级管理员角色）
        const roleCheckSql = `
            SELECT r.code, r.is_system, r.is_protected, r.create_by
            FROM role r 
            WHERE r.id = ? AND r.is_delete = 0
        `;


        const [roleCheckResult] = await db.execute(roleCheckSql, [id]);
        const role = roleCheckResult[0];

        if (!role) {
            throw new Error('角色不存在');
        }

        // 如果是超级管理员角色，检查当前用户是否为超级管理员
        if (isSuperAdminRole(role)) {
            if (currentUserId) {
                const isSuperAdminUser = await checkIfSuperAdmin(currentUserId, db, logger);
                if (!isSuperAdminUser) {
                    throw new Error('无权更新超级管理员角色');
                }
            } else {
                throw new Error('无权更新超级管理员角色');
            }
        }

        // 系统角色不能修改权限层级
        if (isSuperAdminRole(role) && permission_level !== undefined) {
            throw new Error('系统角色的权限层级不能修改');
        }

        let sql, params;
        if (isSuperAdminRole(role)) {
            sql = `
                UPDATE role 
                SET name = ?, code = ?, role_type = ?, data_scope = ?, data_scope_dept_ids = ?, dept_id = ?, description = ?, status = ?, is_protected = ?, remark = ?, effective_time = ?, expire_time = ?, update_by = ?, updated_at = NOW()
                WHERE id = ? AND is_delete = 0
            `;
            params = [
                name !== undefined ? name : role.name,
                code !== undefined ? code : role.code,
                role_type !== undefined ? role_type : role.role_type,
                data_scope !== undefined ? data_scope : role.data_scope,
                data_scope_dept_ids !== undefined ? data_scope_dept_ids : role.data_scope_dept_ids,
                dept_id !== undefined ? dept_id : role.dept_id,
                description !== undefined ? description : role.description,
                status !== undefined ? status : role.status,
                is_protected !== undefined ? is_protected : role.is_protected,
                remark !== undefined ? remark : role.remark,
                effective_time !== undefined ? effective_time : role.effective_time,
                expire_time !== undefined ? expire_time : role.expire_time,
                update_by,
                id
            ];
        } else {
            sql = `
                UPDATE role 
                SET name = ?, code = ?, role_type = ?, data_scope = ?, data_scope_dept_ids = ?, dept_id = ?, description = ?, status = ?, is_protected = ?, permission_level = ?, remark = ?, effective_time = ?, expire_time = ?, update_by = ?, updated_at = NOW()
                WHERE id = ? AND is_delete = 0
            `;
            params = [
                name !== undefined ? name : role.name,
                code !== undefined ? code : role.code,
                role_type !== undefined ? role_type : role.role_type,
                data_scope !== undefined ? data_scope : role.data_scope,
                data_scope_dept_ids !== undefined ? data_scope_dept_ids : role.data_scope_dept_ids,
                dept_id !== undefined ? dept_id : role.dept_id,
                description !== undefined ? description : role.description,
                status !== undefined ? status : role.status,
                is_protected !== undefined ? is_protected : role.is_protected,
                permission_level !== undefined ? permission_level : role.permission_level,
                remark !== undefined ? remark : role.remark,
                effective_time !== undefined ? effective_time : role.effective_time,
                expire_time !== undefined ? expire_time : role.expire_time,
                update_by,
                id
            ];
        }

        try {
            const [result] = await db.execute(sql, params);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新角色失败', { error, updateData, id });
            throw error;
        }
    }

    // 获取绑定角色的所有用户
    static async getRoleUsers(roleId) {
        const sql = `
            SELECT user_id 
            FROM user_role 
            WHERE role_id = ?
        `;

        try {
            const [rows] = await db.execute(sql, [roleId]);
            return rows;
        } catch (error) {
            logger.error('获取角色用户列表失败', { error });
            throw error;
        }
    }

    // 删除角色（软删除）
    static async deleteById(id, currentUserId = null) {
        // 查询角色信息（用于检查是否为超级管理员角色）
        const roleCheckSql = `
            SELECT r.code, r.is_system, r.is_protected 
            FROM role r 
            WHERE r.id = ? AND r.is_delete = 0
        `;
        const [roleCheckResult] = await db.execute(roleCheckSql, [id]);
        const role = roleCheckResult[0];

        // 如果是超级管理员角色，检查当前用户是否为超级管理员
        if (isSuperAdminRole(role)) {
            if (currentUserId) {
                // 即使是超级管理员也不允许删除超级管理员角色
                throw new Error('超级管理员角色不可删除');
            } else {
                throw new Error('超级管理员角色不可删除');
            }
        }

        const sql = `
            UPDATE role 
            SET is_delete = 1, updated_at = NOW(), update_by = ?
            WHERE id = ? AND is_delete = 0 AND is_protected = 0
        `;

        try {
            // 执行删除操作
            const [result] = await db.execute(sql, [currentUserId, id]);
            return result.affectedRows;
        } catch (error) {
            logger.error('删除角色失败', { error });
            throw error;
        }
    }

    // 批量删除角色（软删除）
    static async deleteBatch(ids, currentUserId = null) {
        // 确保ids是数组且处理可能的字符串形式数组
        let processedIds = [];
        if (typeof ids === 'string') {
            // 处理字符串形式的数组，如'[12,11,10]'
            try {
                processedIds = JSON.parse(ids);
                if (!Array.isArray(processedIds)) {
                    throw new Error('角色ID格式无效');
                }
            } catch (e) {
                throw new Error('角色ID列表格式无效');
            }
        } else if (Array.isArray(ids)) {
            processedIds = ids;
        } else {
            throw new Error('角色ID列表必须是数组或JSON字符串数组');
        }

        if (processedIds.length === 0) {
            throw new Error('角色ID列表不能为空');
        }

        // 确保所有ID都是有效的数字
        processedIds = processedIds.map(id => Number(id)).filter(id => !isNaN(id) && id > 0);
        if (processedIds.length === 0) {
            throw new Error('角色ID列表中没有有效的ID');
        }

        // 检查当前用户是否为超级管理员
        const isSuperAdminUser = currentUserId ? await checkIfSuperAdmin(currentUserId, db, logger) : false;

        // 检查是否包含超级管理员角色（条件与isSuperAdminRole函数保持完全一致）
        // 为IN子句创建正确的占位符
        const placeholders = processedIds.map(() => '?').join(',');
        const superAdminCheckSql = `
            SELECT id FROM role 
            WHERE id IN (${placeholders}) AND is_delete = 0 
            AND (code = 'SUPER_ADMIN' OR (is_system = 1 AND is_protected = 1))
        `;
        const [superAdminResult] = await db.execute(superAdminCheckSql, processedIds);
        if (superAdminResult.length > 0) {
            throw new Error('超级管理员角色不可删除');
        }

        // 检查所有角色是否可删除
        for (const id of processedIds) {
            const deletable = await this.isDeletable(id);
            if (!deletable) {
                throw new Error(`角色ID ${id} 不可删除（受保护或已绑定用户/菜单）`);
            }
        }

        // 批量删除角色
        const sql = `
            UPDATE role 
            SET is_delete = 1, updated_at = NOW(), update_by = ?
            WHERE id IN (${placeholders}) AND is_delete = 0 AND is_protected = 0
        `;

        try {
            // 添加currentUserId到参数数组
            const params = [currentUserId, ...processedIds];
            const [result] = await db.execute(sql, params);

            // 获取所有被删除角色绑定的用户ID
            const userRolesSql = `
                SELECT DISTINCT user_id FROM user_role WHERE role_id IN (${placeholders})
            `;
            const [userRolesResult] = await db.execute(userRolesSql, processedIds);

            // 清除相关用户的角色缓存和权限缓存
            for (const row of userRolesResult) {
                // 缓存清理逻辑已移至控制器层
            }

            // 删除所有角色相关的菜单和权限绑定
            for (const roleId of processedIds) {
                // 解除所有菜单绑定
                await this.unbindAllMenus(roleId);

                // 移除所有权限绑定
                const PermissionModel = require('./permissionModel');
                await PermissionModel.removeAllPermissionsFromRole(roleId, currentUserId);
            }

            return result.affectedRows;
        } catch (error) {
            logger.error('批量删除角色失败', { error, ids: processedIds });
            throw error;
        }
    }

    // 检查角色是否可以删除
    static async isDeletable(id) {
        // 查询角色信息（用于检查是否为超级管理员角色）
        const roleCheckSql = `
            SELECT r.code, r.is_system, r.is_protected 
            FROM role r 
            WHERE r.id = ? AND r.is_delete = 0
        `;
        const [roleCheckResult] = await db.execute(roleCheckSql, [id]);
        const roleInfo = roleCheckResult[0];

        // 超级管理员角色不可删除
        if (isSuperAdminRole(roleInfo)) {
            return false;
        }

        const sql = `
            SELECT 
                is_protected,
                COUNT(DISTINCT ur.user_id) as user_count,
                COUNT(DISTINCT rm.menu_id) as menu_count
            FROM role r
            LEFT JOIN user_role ur ON r.id = ur.role_id
            LEFT JOIN role_menu rm ON r.id = rm.role_id
            WHERE r.id = ? AND r.is_delete = 0
            GROUP BY r.id, r.is_protected
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            if (rows.length === 0) return false;

            const roleData = rows[0];
            return roleData.is_protected === 0 || (roleData.user_count === 0 && roleData.menu_count === 0); // 只有非保护角色或者未绑定用户和菜单才能删除
        } catch (error) {
            logger.error('检查角色是否可删除失败', { error });
            throw error;
        }
    }

    // 获取用户已绑定的角色
    static async getBindRoles(page = 1, limit = 10, filters = {}) {
        const offset = (page - 1) * limit;
        let whereClause = 'WHERE r.is_delete = 0';
        let params = [];

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND r.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND r.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND r.status = ?';
            params.push(filters.status);
        }
        if (filters.is_protected !== undefined) {
            whereClause += ' AND r.is_protected = ?';
            params.push(filters.is_protected);
        }
        if (filters.role_type !== undefined) {
            whereClause += ' AND r.role_type = ?';
            params.push(filters.role_type);
        }

        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.dept_id,
                d.name as department_name,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.create_by,
                r.update_by,
                r.remark,
                r.effective_time,
                r.expire_time,
                ur.created_at as bind_time,
                ur.id as link_id
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            LEFT JOIN department d ON r.dept_id = d.id AND d.is_delete = 0
            ${whereClause}
            AND ur.user_id = ?
            ORDER BY r.created_at DESC
            LIMIT ${Number(limit)} OFFSET ${Number(offset)}
        `;

        try {
            // 将用户ID添加到参数数组中
            if (!filters.userid) {
                throw new Error('用户ID不能为空');
            }
            params.push(filters.userid);
            const [rows] = await db.execute(sql, params);
            return rows;
        } catch (error) {
            logger.error('获取用户已绑定角色失败', { error });
            throw error;
        }
    }

    // 获取用户已绑定的角色数量
    static async getBindRoleCount(filters = {}) {
        let whereClause = 'WHERE r.is_delete = 0';
        let params = [];

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND r.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND r.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.status !== undefined) {
            whereClause += ' AND r.status = ?';
            params.push(filters.status);
        }
        if (filters.is_protected !== undefined) {
            whereClause += ' AND r.is_protected = ?';
            params.push(filters.is_protected);
        }
        if (filters.role_type !== undefined) {
            whereClause += ' AND r.role_type = ?';
            params.push(filters.role_type);
        }

        const sql = `
            SELECT COUNT(*) as count
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            ${whereClause}
            AND ur.user_id = ?
        `;

        try {
            // 将用户ID添加到参数数组中
            if (!filters.userid) {
                throw new Error('用户ID不能为空');
            }
            params.push(filters.userid);
            const [rows] = await db.execute(sql, params);
            return rows[0].count;
        } catch (error) {
            logger.error('获取用户已绑定角色数量失败', { error });
            throw error;
        }
    }


    // 根据用户ID获取角色
    static async findRolesByUserId(userId) {
        const sql = `
            SELECT 
                r.id,
                r.name,
                r.code,
                r.role_type,
                r.data_scope,
                r.dept_id,
                d.name as department_name,
                r.description,
                r.status,
                r.is_protected,
                r.is_system,
                r.permission_level,
                r.remark,
                r.effective_time,
                r.expire_time,
                ur.created_at as bind_time
            FROM role r
            INNER JOIN user_role ur ON r.id = ur.role_id
            LEFT JOIN department d ON r.dept_id = d.id AND d.is_delete = 0
            WHERE ur.user_id = ? AND r.is_delete = 0 AND NOT (r.code = 'SUPER_ADMIN' OR (r.is_system = 1 AND r.is_protected = 1))
            ORDER BY r.permission_level DESC, ur.created_at DESC
        `;

        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows;
        } catch (error) {
            logger.error('根据用户ID获取角色失败', { error });
            throw error;
        }
    }

    // 角色绑定用户
    static async bindUsers(roleId, userIds, currentUserId) {
        if (!Array.isArray(userIds) || userIds.length === 0) {
            return 0;
        }
        const createdBy = currentUserId;
        const updatedBy = currentUserId;

        // 先删除现有绑定
        await this.unbindAllUsers(roleId);

        // 插入新的绑定
        const placeholders = userIds.map(() => '(?, ?, ?, ?)').join(',');
        const values = [];
        userIds.forEach(userId => {
            values.push(userId, roleId, createdBy, updatedBy);
        });
        const sql = `INSERT INTO user_role (user_id, role_id, create_by, update_by) VALUES ${placeholders}`;

        try {
            const [result] = await db.execute(sql, values);
            return result.affectedRows;
        } catch (error) {
            logger.error('角色绑定用户失败', { error });
            throw error;
        }
    }

    // 解除角色所有用户绑定
    static async unbindAllUsers(roleId) {
        const sql = 'DELETE FROM user_role WHERE role_id = ?';

        try {
            const [result] = await db.execute(sql, [roleId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('解除角色所有用户绑定失败', { error });
            throw error;
        }
    }


    // 删除用户角色关联数据
    static async deleteByUserId(userId, connection = db) {
        const sql = `DELETE FROM user_role WHERE user_id = ?`;
        try {
            const [result] = await connection.execute(sql, [userId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('删除用户角色关联失败', { error });
            throw error;
        }
    }

    // 绑定单个角色到用户
    static async bindUserRole(userId, roleId, currentUserId = null, connection = db) {
        // 查询角色信息，获取权限层级
        const roleSql = `
            SELECT id, code, permission_level, is_system, is_protected 
            FROM role 
            WHERE id = ? AND is_delete = 0
        `;
        const [roleResult] = await connection.execute(roleSql, [roleId]);
        const role = roleResult[0];

        if (!role) {
            throw new Error('角色不存在或已被删除');
        }

        // 检查是否为超级管理员角色
        if (isSuperAdminRole(role)) {
            throw new Error('超级管理员角色不允许通过此接口绑定');
        }

        // 先删除该用户的所有现有角色绑定
        await this.deleteByUserId(userId, connection);

        // 插入新的角色绑定
        const sql = `INSERT INTO user_role (user_id, role_id, create_by, update_by) VALUES (?, ?, ?, ?)`;
        try {
            const [result] = await connection.execute(sql, [userId, roleId, currentUserId, currentUserId]);
            // 清除用户相关缓存
            
            await cacheService.del(generateCacheKey('user','roles', userId));
            await cacheService.del(generateCacheKey('user','permissions', userId));
            await cacheService.del(generateCacheKey('user','permission_level', userId));
            logger.info('用户角色绑定成功，包含权限层级信息', { userId, roleId, permissionLevel: role.permission_level });
            return result.insertId;
        } catch (error) {
            logger.error('绑定用户角色失败', { error, userId, roleId });
            throw error;
        }
    }

    // 角色绑定菜单
    static async bindMenus(roleId, menuIds, currentUserId = null) {
        if (!Array.isArray(menuIds) || menuIds.length === 0) {
            return 0;
        }


        // 获取所有绑定该角色的用户ID
        const userRoles = await this.getRoleUsers(roleId);

        // 先删除现有绑定
        await this.unbindAllMenus(roleId);


        // 插入新的绑定
        const values = menuIds.map(menuId => [menuId, roleId, currentUserId, currentUserId]);

        const placeholders = values.map(() => '(?, ?, ?, ?)').join(',');
        const sql = `INSERT INTO role_menu (menu_id, role_id, create_by, update_by) VALUES ${placeholders}`;
        const flattenedValues = values.flat();

        try {

            const [result] = await db.execute(sql, flattenedValues);

            // 清除相关用户的缓存
            for (const { user_id } of userRoles) {
                await cacheService.clearByPattern(`user_roles:${user_id}`);
                await cacheService.del(`user:permissions:${user_id}`);
            }

            return result.affectedRows;
        } catch (error) {
            logger.error('角色绑定菜单失败', { error });
            throw error;
        }
    }

    // 解除角色所有菜单绑定
    static async unbindAllMenus(roleId) {
        const sql = 'DELETE FROM role_menu WHERE role_id = ?';

        // 获取所有绑定该角色的用户ID
        const userRoles = await this.getRoleUsers(roleId);

        try {
            const [result] = await db.execute(sql, [roleId]);

            // 如果删除成功，清除相关缓存
            if (result.affectedRows > 0) {
                for (const { user_id } of userRoles) {
                    await cacheService.del(generateCacheKey('user','roles', user_id));
                    await cacheService.del(generateCacheKey('user','permissions', user_id));
                }
            }

            return result.affectedRows;
        } catch (error) {
            logger.error('解除角色所有菜单绑定失败', { error });
            throw error;
        }
    }

    // 获取角色已绑定的菜单
    static async getBindMenus(roleId, currentUserId = null, isSuperAdmin = false) {
        // 参数验证
        if (!roleId || roleId === 'undefined' || roleId === 'null') {
            throw new Error('角色ID不能为空');
        }

        // 超级管理员可以查看所有菜单
        let sql;
        let params = [];

        if (isSuperAdmin) {
            sql = `
                SELECT 
                    m.id,
                    m.name,
                    m.code,
                    m.node_type,
                    m.icon,
                    m.component_path,
                    m.route_path,
                    m.parent_id,
                    m.path,
                    m.status,
                    rm.created_at as bind_time
                FROM menu m
                LEFT JOIN role_menu rm ON m.id = rm.menu_id AND rm.role_id = ?
                WHERE m.is_delete = 0 
                ORDER BY m.parent_id ASC, m.id ASC
            `;
            params.push(Number(roleId));
        } else {
            sql = `
                SELECT 
                    m.id,
                    m.name,
                    m.code,
                    m.node_type,
                    m.icon,
                    m.component_path,
                    m.route_path,
                    m.parent_id,
                    m.path,
                    m.status,
                    rm.created_at as bind_time
                FROM menu m
                INNER JOIN role_menu rm ON m.id = rm.menu_id
                WHERE m.is_delete = 0 
                AND rm.role_id = ?
                ORDER BY m.parent_id ASC, m.id ASC
            `;
            params.push(Number(roleId));
        }

        try {
            const [rows] = await db.execute(sql, params);
            return rows;
        } catch (error) {
            logger.error('获取角色已绑定菜单失败', { error, roleId, sql });
            throw error;
        }
    }

    // 获取角色已绑定的用户
    static async getBindUsers(roleId, page = 1, limit = 10, filters = {}) {
        const offset = (page - 1) * limit;
        // const { username, email, phone, status } = filters;
        // 参数验证
        if (!roleId || roleId === 'undefined' || roleId === 'null') {
            throw new Error('角色ID不能为空');
        }

        // let whereClause = 'WHERE r.is_delete = 0';
        // let params = [];

        const sql = `
            SELECT 
                u.id,
                u.username,                         
                u.code,
                u.name,
                u.phone,
                u.email,
                u.address,
                u.department_id,
                u.avatar_url,
                u.status,
                u.is_protected,
                u.gender,
                u.created_at,
                u.updated_at,
                u.create_by,
                u.update_by,
                (SELECT name FROM user WHERE id = u.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = u.update_by AND is_delete = 0) as update_by_name,
                d.name as department_name,
                d.code as department_code,
                d.type as department_type
            FROM user u
            INNER JOIN user_role ur ON u.id = ur.user_id
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            WHERE ur.role_id = ? AND u.is_delete = 0
            LIMIT ${Number(limit)} OFFSET ${Number(offset)}

        `;

        try {
            const [rows] = await db.execute(sql, [Number(roleId)]);
            return rows;
        } catch (error) {
            logger.error('获取角色已绑定用户失败', { error, roleId, sql });
            throw error;
        }
    }

    // 获取角色已绑定的用户数量
    static async getBindUsersCount(roleId, filters = {}) {
        const sql = `
            SELECT COUNT(*) as count
            FROM user u
            INNER JOIN user_role ur ON u.id = ur.user_id
            WHERE ur.role_id = ? AND u.is_delete = 0
        `;
        try {
            const [rows] = await db.execute(sql, [roleId]);
            return rows[0].count;
        } catch (error) {
            logger.error('获取角色已绑定用户数量失败', { error });
            throw error;
        }
    }

    // 获取角色已绑定的权限
    static async getBindPermissions(roleId, currentUserId = null) {

        // 参数验证
        if (!roleId || roleId === 'undefined' || roleId === 'null') {
            throw new Error('角色ID不能为空');
        }

        const sql = `
            SELECT 
                p.id,
                p.permission_name,
                p.permission_code,
                p.module_code,
                p.permission_type,
                p.operation_type,
                p.api_path,
                p.api_method,
                p.status,
                rm.created_at as bind_time
            FROM permission p
            INNER JOIN role_permission rm ON p.id = rm.permission_id
            WHERE p.is_delete = 0 
            AND rm.role_id = ?
            ORDER BY p.module_code ASC, p.id ASC
        `;

        try {
            const [rows] = await db.execute(sql, [Number(roleId)]);
            return rows;
        } catch (error) {
            logger.error('获取角色已绑定权限失败', { error, roleId, sql });
            throw error;
        }
    }



    // 获取角色可绑定的菜单
    static async getCanBindMenus(roleId) {

        // 参数验证
        if (!roleId || roleId === 'undefined' || roleId === 'null') {
            throw new Error('角色ID不能为空');
        }

        const sql = `
            SELECT 
                m.id,
                m.name,
                m.code,
                m.node_type,
                m.icon,
                m.component_path,
                m.route_path,
                m.parent_id,
                m.path,
                m.status,
                CASE WHEN rm.role_id IS NOT NULL THEN 1 ELSE 0 END as is_bound
            FROM menu m
            LEFT JOIN role_menu rm ON m.id = rm.menu_id AND rm.role_id = ?
            WHERE m.is_delete = 0
            ORDER BY m.parent_id ASC, m.id ASC
        `;

        try {
            const [rows] = await db.execute(sql, [Number(roleId)]);
            return rows;
        } catch (error) {
            logger.error('获取角色可绑定菜单失败', { error, roleId, sql });
            throw error;
        }
    }

    // 获取角色统计信息
    static async getRoleStats() {
        const sql = `
            SELECT 
                COUNT(*) as total_roles,
                COUNT(CASE WHEN status = 0 THEN 1 END) as active_roles,
                COUNT(CASE WHEN status = 1 THEN 1 END) as inactive_roles,
                COUNT(CASE WHEN is_protected = 1 THEN 1 END) as protected_roles,
                COUNT(CASE WHEN is_protected = 0 THEN 1 END) as normal_roles,
                COUNT(DISTINCT ur.user_id) as users_with_roles,
                COUNT(DISTINCT rm.menu_id) as menus_with_roles
            FROM role r
            LEFT JOIN user_role ur ON r.id = ur.role_id
            LEFT JOIN role_menu rm ON r.id = rm.role_id
            WHERE r.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows[0];
        } catch (error) {
            logger.error('获取角色统计信息失败', { error });
            throw error;
        }
    }

    // 判断当前用户的权限菜单是否变化更新（判断是否需要更新前端menu）
    static async checkRoleUpdate(userId) {
        try {
            // 1. 检查用户角色是否发生变化
            const rolesChanged = await this.hasRoleChanged(userId);

            // 2. 检查菜单数量（用于基础判断）
            const sql = `
                SELECT 
                    COUNT(*) as menu_count
                FROM menu m
                INNER JOIN role_menu rm ON m.id = rm.menu_id
                INNER JOIN user_role ur ON rm.role_id = ur.role_id
                WHERE m.is_delete = 0 AND ur.user_id = ?
            `;

            const [rows] = await db.execute(sql, [userId]);
            const menuCount = rows[0].menu_count;

            // 3. 综合判断：角色变化或有菜单权限时返回true（需要更新）
            const needUpdate = rolesChanged || menuCount > 0;

            logger.info('角色权限更新检查结果', {
                userId,
                rolesChanged,
                menuCount,
                needUpdate
            });

            return needUpdate;
        } catch (error) {
            logger.error('检查角色菜单更新状态失败', { userId, error });
            // 出错时返回false，避免因检查失败影响正常功能
            return false;
        }
    }

    // 获取用户当前绑定的所有角色信息
    static async getCurrentRoles(userId) {
        const sql = `
            SELECT r.id, r.code, r.permission_level 
            FROM user_role ur 
            JOIN role r ON ur.role_id = r.id 
            WHERE ur.user_id = ? AND r.is_delete = 0
            ORDER BY ur.created_at DESC
        `;

        try {
            const [rows] = await db.execute(sql, [userId]);
            // 按权限层级降序排序，确保高权限角色在前
            return rows.sort((a, b) => b.permission_level - a.permission_level);
        } catch (error) {
            logger.error('获取用户当前角色失败', { userId, error });
            throw error;
        }
    }

    // 检测用户角色是否发生变化
    static async hasRoleChanged(userId) {
        try {
            // 获取用户当前角色列表
            const currentRoles = await this.getCurrentRoles(userId);
            // 缓存相关逻辑已移至控制器层
            // 比对角色是否有变化
            const hasChanged = this.compareRoles(currentRoles, null);
            return hasChanged;
        } catch (error) {
            logger.error('检测用户角色变化失败', { userId, error });
            // 出错时返回false，避免因缓存问题影响正常功能
            return false;
        }
    }

    // 比对两个角色列表是否相同
    static compareRoles(roles1, roles2) {
        // 如果roles2为null（首次检查），返回true表示有变化
        if (!roles2) {
            return true;
        }
        
        // 角色数量不同，直接返回true（有变化）
        if (roles1.length !== roles2.length) {
            return true;
        }

        // 创建角色ID的Set用于快速比对
        const roleIds1 = new Set(roles1.map(r => r.id));
        const roleIds2 = new Set(roles2.map(r => r.id));

        // 检查是否所有角色ID都相同
        for (const id of roleIds1) {
            if (!roleIds2.has(id)) {
                return true;
            }
        }

        return false;
    }

    // 获取用户的最大权限级别
    static async getUserMaxPermissionLevel(userId) {
        const sql = `
            SELECT MAX(r.permission_level) as max_level 
            FROM user_role ur
            JOIN role r ON ur.role_id = r.id
            WHERE ur.user_id = ?
        `;

        try {
            const [results] = await db.execute(sql, [userId]);
            return results[0].max_level || 0;
        } catch (error) {
            logger.error('获取用户最大权限级别失败', { userId, error });
            throw error;
        }
    }

    // 获取角色的权限级别和系统标记
    static async getRolePermissionInfo(id) {
        const sql = 'SELECT permission_level, is_system FROM role WHERE id = ?';

        try {
            const [results] = await db.execute(sql, [id]);
            return results[0] || null;
        } catch (error) {
            logger.error('获取角色权限信息失败', { id, error });
            throw error;
        }
    }

    // 获取角色的权限级别
    static async getRolePermissionLevel(id) {
        const sql = 'SELECT permission_level FROM role WHERE id = ?';

        try {
            const [results] = await db.execute(sql, [id]);
            return results[0] || null;
        } catch (error) {
            logger.error('获取角色权限级别失败', { id, error });
            throw error;
        }
    }
}

module.exports = RoleModel;